package DBLayer;
import ModelLayer.Category;
import java.sql.*;
import java.util.ArrayList;

public class DBCategory {
    // <editor-fold defaultstate="collapsed" desc="Attributes & Constructors">
    private Connection conn;

    public DBCategory() {
        conn = SQLConnector.getInstance().getConnection();
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="SQL SELECT Methods">
    public Category getCategoryByID(int id) {
        return selectSingle("SELECT `id_category`, `id_parent`, `level_depth`, `active`, `sort`, UNIX_TIMESTAMP(`date_add`) AS `date_add`, UNIX_TIMESTAMP(`date_upd`) AS `date_upd`, `name`, `description`, `link_rewrite`, `meta_title`, `meta_keywords`, `meta_description` FROM `ps_category` LEFT JOIN `ps_category_lang` USING (`id_category`) WHERE (`id_lang` = " + Constants.DEFAULT_LANGUAGE + " OR `id_lang` IS NULL) AND `id_category` = " + id + " LIMIT 0, 1;");
    }

    public ArrayList<Category> getCategoryList() {
        return selectMultiple("SELECT `id_category`, `id_parent`, `level_depth`, `active`, `sort`, UNIX_TIMESTAMP(`date_add`) AS `date_add`, UNIX_TIMESTAMP(`date_upd`) AS `date_upd`, `name`, `description`, `link_rewrite`, `meta_title`, `meta_keywords`, `meta_description` FROM `ps_category` LEFT JOIN `ps_category_lang` USING (`id_category`) WHERE `id_lang` = " + Constants.DEFAULT_LANGUAGE + " OR `id_lang` IS NULL ORDER BY `id_category` ASC;");
    }

    public ArrayList<Integer> getCategoryIDsByProductID(int id) {
        return selectIDs("SELECT `id_category` FROM `ps_category_product` WHERE `id_product` = " + id + " ORDER BY `id_category` ASC;");
    }

    public ArrayList<Integer> getCategoryIDsByDiscountID(int id) {
        return selectIDs("SELECT `id_category` FROM `ps_discount_category` WHERE `id_discount` = " + id + " ORDER BY `id_category` ASC;");
    }

    private Category selectSingle(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        Category category = new Category();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            if (result.next()) {
                category = buildCategory(result);
            }
            System.out.println("Fetching Category Object");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return category;
    }

    private ArrayList<Category> selectMultiple(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        ArrayList<Category> categories = new ArrayList<Category>();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            while (result.next()) {
                Category category = new Category();
                category = buildCategory(result);
                categories.add(category);
            }
            System.out.println("Fetching Category List");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return categories;
    }

    private ArrayList<Integer> selectIDs(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        ArrayList<Integer> ids = new ArrayList<Integer>();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            while (result.next()) {
                ids.add(result.getInt("id_category"));
            }
            System.out.println("Fetching Category ID List");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return ids;
    }

    private Category buildCategory(ResultSet result) {
        DBProduct dbProduct = new DBProduct();
        DBDiscount dbDiscount = new DBDiscount();
        Category category = new Category();
        try {
            category.setIDCategory(result.getInt("id_category"));
            category.setIDParent(result.getInt("id_parent"));
            category.setLevelDepth(result.getInt("level_depth"));
            if (result.getInt("active") == 1) {
                category.setActive(true);
            } else {
                category.setActive(false);
            }
            category.setSort(result.getInt("sort"));
            category.setDateAdd(result.getInt("date_add"));
            category.setDateUpd(result.getInt("date_upd"));
            category.setName(result.getString("name"));
            category.setDescription(result.getString("description"));
            category.setLinkRewrite(result.getString("link_rewrite"));
            category.setMetaTitle(result.getString("meta_title"));
            category.setMetaKeywords(result.getString("meta_keywords"));
            category.setMetaDescription(result.getString("meta_description"));
            category.setProductIDs(dbProduct.getProductIDsByCategoryID(result.getInt("id_category")));
            category.setDiscountIDs(dbDiscount.getDiscountIDsByCategoryID(result.getInt("id_category")));

        } catch (Exception e) {
            System.out.println("Exception: " + e.getMessage());
        }
        return category;
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="SQL INSERT, UPDATE & DELETE Methods">
    public int addCategory(Category category) {
        int returnID = 0;
        Statement stmt = null;
        ResultSet result = null;

        String query = "INSERT INTO `ps_category_lang` (`id_category`, `id_lang`, `name`, `description`, `link_rewrite`, `meta_title`, `meta_keywords`, `meta_description`) "
                + "VALUES (" + category.getIDCategory() + ", " + Constants.DEFAULT_LANGUAGE + ", '" + category.getName() + "', '" + category.getDescription() + "', '" + category.getLinkRewrite() + "', '" + category.getMetaTitle() + "', '" + category.getMetaKeywords() + "', '" + category.getMetaDescription() + "');";

        query += "INSERT INTO `ps_category` (`id_category`, `id_parent`, `level_depth`, `sort`, `date_add`, `date_upd`, `active`) "
                + "VALUES (" + category.getIDCategory() + ", " + category.getIDParent() + ", " + category.getLevelDepth() + ", " + category.getSort() + ", FROM_UNIXTIME(" + category.getDateAdd() + "), FROM_UNIXTIME(" + category.getDateUpd() + "), ";
        if (category.getActive() == true)       { query += "1);"; }
        else                                    { query += "0);"; }
        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
            result = stmt.getGeneratedKeys();
            if (result.next()) {
                returnID = result.getInt(1);
            }
            System.out.println("Added Category ID " + returnID);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return returnID;
    }

    public int updateCategory(Category category, int id) {
        int returnID = 0;
        Statement stmt = null;
	String query = "UPDATE `ps_category` SET "
                + "`id_category`      = "  + category.getIDCategory() + ", "
                + "`id_parent`        = "  + category.getIDParent() + ", "
                + "`level_depth`      = "  + category.getLevelDepth() + ", ";
        if (category.getActive() == true)   { query += "`active` = 1, "; }
        else                                { query += "`active` = 0, "; }
        query +=  "`date_add`         = FROM_UNIXTIME("  + category.getDateAdd() + "), "
                + "`date_upd`         = FROM_UNIXTIME("  + category.getDateUpd() + ") "
                + "WHERE `id_category` = " + id + " LIMIT 1; ";

        query += "UPDATE `ps_category_lang` SET "
                + "`name`             = '" + category.getName() + "', "
                + "`description`      = '" + category.getDescription() + "', "
                + "`link_rewrite`     = '" + category.getLinkRewrite() + "', "
                + "`meta_title`       = '" + category.getMetaTitle() + "', "
                + "`meta_keywords`    = '" + category.getMetaKeywords() + "', "
                + "`meta_description` = '" + category.getMetaDescription() + "' ";
	query += "WHERE `id_lang` = " + Constants.DEFAULT_LANGUAGE + " AND `id_category` = " + id + " LIMIT 1;";

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(query);
            returnID = category.getIDCategory();
            System.out.println("Updated Category ID " + id);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return returnID;
    }

    public void deleteCategory(int id) {
        Statement stmt = null;

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate("DELETE FROM `ps_category` WHERE `id_category` = " + id + " LIMIT 1; DELETE FROM `ps_category_lang` WHERE `id_lang` = " + Constants.DEFAULT_LANGUAGE + " AND `id_category` = " + id + " LIMIT 1;");
            System.out.println("Deleted Category ID " + id);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
    }
    // </editor-fold>
}
